package mysql

import (
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
	"go-money/dao"
)

var db *sqlx.DB

func InitDB() error {
	var err error
	dsn := "test:123456@tcp(210.42.37.244:3306)/test?charset=utf8mb4&parseTime=True"
	db, err = sqlx.Connect("mysql", dsn) //进行连接
	if err != nil {
		fmt.Printf("connect DB failed, err:%v\n", err)
		return err
	}
	return nil
}

func Form(identity int, stu_id, name, school, college, field string) error {
	sqlStr := "insert into re(identity,stu_id,name,school,college,field,time) values(?,?,?,?,?,?,NOW())"
	_, err := db.Exec(sqlStr, identity, stu_id, name, school, college, field)
	if err != nil {
		fmt.Printf("Forminsert failed, err:%v\n", err)
		return err
	}
	return nil
}

func Searchtime(stu_id string, name string) error {
	sqlStr := "select * from re where stu_id=? and TIMESTAMPDIFF(HOUR,time,NOW())<1 and name=?;"
	var all dao.Stu
	err := db.Get(&all, sqlStr, stu_id, name)
	if err != nil {
		fmt.Printf("Searchtime failed, err:%v\n", err)
		return err
	}
	if all != (dao.Stu{}) {
		fmt.Printf("Searchtime2 failed, err:%v\n", err)
		return err
	}
	return nil
}

func Addcollege(college string) error {
	sql := "select flag from col where college = ?"
	var flag int
	err := db.Get(&flag, sql, college)
	fmt.Println(flag)
	if flag == 1 {
		sqlstr := "update col set flag=0 where college =?"
		_, err = db.Exec(sqlstr, college)
		if err != nil {
			fmt.Printf("Addcollege2 failed, err:%v\n", err)
			return err
		}
		return nil
	}
	sqlStr := "insert into col(college) value (?)"
	_, err = db.Exec(sqlStr, college)
	if err != nil {
		fmt.Printf("Addcollege3 failed, err:%v\n", err)
		return err
	}
	return nil
}

func Deletecollege(college string) error {
	sqlStr := "update col set flag=1 where college=?"
	_, err := db.Exec(sqlStr, college)
	if err != nil {
		fmt.Printf("Deletecollege failed, err:%v\n", err)
		return err
	}
	return nil
}

//func Updatecollege(college1, college2 string) error {
//	sqlStr := "update col set college=? where college=?"
//	_, err := db.Exec(sqlStr, college1, college2)
//	if err != nil {
//		fmt.Printf("Updatecollege failed, err:%v\n", err)
//		return err
//	}
//	return nil
//}

func Searchcollege() ([]dao.Col, error) {
	sqlStr := "select * from col"
	var all []dao.Col
	err := db.Select(&all, sqlStr)
	if err != nil {
		fmt.Printf("Searchcollege failed, err:%v\n", err)
		return all, err
	}
	return all, nil
}

func Searchc() ([]dao.Col, error) {
	sqlStr := "select * from col where flag=0"
	var all []dao.Col
	err := db.Select(&all, sqlStr)
	if err != nil {
		fmt.Printf("Searchc failed, err:%v\n", err)
		return all, err
	}
	return all, nil
}

func Addfield(field string) error {
	sql := "select flag from field where field =?"
	var flag int
	err := db.Get(&flag, sql, field)
	if flag == 1 {
		sqlstr := "update field set flag=0,time=NOW() where field=?"
		_, err = db.Exec(sqlstr, field)
		if err != nil {
			fmt.Printf("Addfield1 failed, err:%v\n", err)
			return err
		}
		return nil
	}
	sqlStr := "insert into field(field,time) value (?,NOW())"
	_, err = db.Exec(sqlStr, field)
	if err != nil {
		fmt.Printf("Addfield2 failed, err:%v\n", err)
		return err
	}
	return nil
}

func Deletefield(field string) error {
	sqlStr := "update field set flag=1 where field=?"
	_, err := db.Exec(sqlStr, field)
	if err != nil {
		fmt.Printf("Addfield failed, err:%v\n", err)
		return err
	}
	return nil
}
func Searchfield() ([]dao.Field, error) {
	var all []dao.Field
	sqlStr := "select * from field ORDER BY time DESC"
	err := db.Select(&all, sqlStr)
	if err != nil {
		fmt.Printf("Searchfield failed, err:%v\n", err)
		return all, err
	}
	return all, nil
}

func Searchf() ([]dao.Field, error) {
	sqlStr := "select * from field where flag=0 ORDER BY time DESC"
	var all []dao.Field
	err := db.Select(&all, sqlStr)
	if err != nil {
		fmt.Printf("Searchf failed, err:%v\n", err)
		return all, err
	}
	return all, nil
}

//func Formname(name string) ([]dao.Stu, error) {
//	var all []dao.Stu
//	sqlStr := "select * from re where name like ?"
//	err := db.Select(&all, sqlStr, "%"+name+"%")
//	if err != nil {
//		fmt.Printf("Formname failed, err:%v\n", err)
//		return all, err
//	}
//	return all, nil
//}

//func Formcollege(college string) ([]dao.Stu, error) {
//	var all []dao.Stu
//	sqlStr := "select * from re where college like ?"
//	err := db.Select(&all, sqlStr, "%"+college+"%")
//	if err != nil {
//		fmt.Printf("Formcollege failed, err:%v\n", err)
//		return all, err
//	}
//	return all, nil
//}

//func Formfield(field string) ([]dao.Stu, error) {
//	var all []dao.Stu
//	sqlStr := "select * from re where field like ?"
//	err := db.Select(&all, sqlStr, "%"+field+"%")
//	if err != nil {
//		fmt.Printf("Formfield failed, err:%v\n", err)
//		return all, err
//	}
//	return all, nil
//}

func Allform() ([]dao.Stu, error) {
	var all []dao.Stu
	sqlStr := "select * from re"
	err := db.Select(&all, sqlStr)
	if err != nil {
		fmt.Printf("Allform failed, err:%v\n", err)
		return all, err
	}
	return all, nil
}

func SearchTime(time1, time2 string) ([]dao.Stu, error) {
	var all []dao.Stu
	fmt.Println(time1, time2)
	sqlStr := "SELECT * from re where time BETWEEN ? AND ?"
	err := db.Select(&all, sqlStr, time1, time2)
	if err != nil {
		fmt.Printf("SearchTime failed, err:%v\n", err)
		return all, err
	}
	return all, nil
}

//func Searchidentity(identity int) ([]dao.Stu, error) {
//	var all []dao.Stu
//	sqlStr := "select * from re where identity=?"
//	err := db.Select(&all, sqlStr, identity)
//	if err != nil {
//		fmt.Printf("Searchidentity failed, err:%v\n", err)
//		return all, err
//	}
//	return all, nil
//}

func Field(field string, time1, time2 string) ([]dao.F, error) {
	var all []dao.F
	sqlStr := "select col.college ,COALESCE(c.num,0) num from col  left JOIN (select college,COUNT(*) num from re where field like ? and time BETWEEN ? AND ? GROUP BY college) c on c.college=col.college where col.flag=0;"
	//sqlStr := "select college,COUNT(*) num from re where field like ? and college in(select college from col) and time BETWEEN ? AND ? GROUP BY college;"
	err := db.Select(&all, sqlStr, "%"+field+"%", time1, time2)
	if err != nil {
		fmt.Printf("Field failed, err:%v\n", err)
		return all, err
	}
	return all, nil
}

//func Searchname(name string, identity int) ([]dao.Stu, error) {
//	var all []dao.Stu
//	sqlStr := "select * from re where name like ? and identity =?"
//	err := db.Select(&all, sqlStr, "%"+name+"%", identity)
//	if err != nil {
//		fmt.Printf("Formname failed, err:%v\n", err)
//		return all, err
//	}
//	return all, nil
//}

func Searchall(identity string, name, college, field string, time1, time2 string) ([]dao.Stu, error) {
	var all []dao.Stu
	fmt.Println(identity, name, college, field, time1, time2)
	sqlStr := "select * from re where identity like ? and name like ? and college like ? and field like ? and time BETWEEN ? AND ?"
	err := db.Select(&all, sqlStr, "%"+identity+"%", "%"+name+"%", "%"+college+"%", "%"+field+"%", time1, time2)
	if err != nil {
		fmt.Printf("Searchall failed, err:%v\n", err)
		return all, err
	}
	return all, nil
}

func Delete_time(time1, time2 string) error {
	sqlStr := "delete from re where time BETWEEN ? AND ?"
	_, err := db.Exec(sqlStr, time1, time2)
	if err != nil {
		fmt.Printf("Delete_time failed, err:%v\n", err)
		return err
	}
	return nil
}
func Delete_c(college string) error {
	sqlStr := "delete from col where college=?"
	_, err := db.Exec(sqlStr, college)
	if err != nil {
		fmt.Printf("Delete_c failed, err:%v\n", err)
		return err
	}
	return nil
}

func Delete_f(field string) error {
	sqlStr := "delete from field where field=?"
	_, err := db.Exec(sqlStr, field)
	if err != nil {
		fmt.Printf("Delete_c failed, err:%v\n", err)
		return err
	}
	return nil
}
